package org.example.dao;

import org.example.entity.Staff;
import org.example.entity.Staff;
import org.example.util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class StaffDao {
    public static ArrayList<Staff> findAllStaff(){

        // 泛型
        ArrayList<Staff> arrayList = new ArrayList<>();

        // 查询数据
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "select * from staff ";

        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);

            // 执行SQL语句
            resultSet = statement.executeQuery();

            while (resultSet.next()){

                String Staffid = resultSet.getString("Id");
                String Sname = resultSet.getString("Sname");
                String Gender = resultSet.getString("Sno");
                String Password = resultSet.getString("Gender");
                String Bookname = resultSet.getString("Prof");
                String Outdata = resultSet.getString("Address");
                String Backdata = resultSet.getString("Grade");

                // 数据持久化 ==> 实体类
                Staff staff = new Staff();
                staff.setId(Staffid);
                staff.setSname(Sname);
                staff.setGender(Gender);
                staff.setSno(Password);
                staff.setProf(Bookname);
                staff.setAddress(Outdata);
                staff.setGrade(Backdata);

                arrayList.add(staff);
            }

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DButil.close(resultSet, statement, connection);
        }

        return arrayList;
    }


    public static boolean  login(String Staffid, String Password){
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "select * from staff where Id = ? and Sname =? ";

        boolean flag = false;

        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);

            // 对SQL进行传参
            statement.setString(1, Staffid);
            statement.setString(2, Password);

            // 执行SQL语句
            resultSet = statement.executeQuery();
//            statement.executeUpdate()
            if (resultSet.next()){
                flag = true;
            }

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DButil.close(resultSet, statement, connection);
        }

        return flag;
    }

    public static int addStaff(Staff staff) {

        // 泛型

        // 查询数据
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "insert into staff (Id,Sname,Sno,Gender,Prof,Address,Grade) values (?,?,?,?,?,?,?)";
        int row = 0;
        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);
            statement.setString(1, staff.getId());
            statement.setString(2, staff.getSname());
            statement.setString(3, staff.getSno());
            statement.setString(4, staff.getGender());
            statement.setString(5, staff.getProf());
            statement.setString(6, staff.getAddress());
            statement.setString(7, staff.getGrade());


            // 执行SQL语句
            // select ----> statement.executeQuery();

            // insert delete update
            row = statement.executeUpdate();

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DButil.close(resultSet, statement, connection);
        }

        return row;
    }

    public static int deleteStaff(String Staffid) {
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "delete from staff where Id=?";
        int row = 0;

        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);
            statement.setString(1, Staffid);

            // 执行SQL语句
            // select ----> statement.executeQuery();

            // insert delete update
            row = statement.executeUpdate();

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DButil.close(resultSet, statement, connection);
        }

        return row;
    }

    public static int updateStaff(Staff staff) {
        Connection connection = DButil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "update staff set Sname=?,Sno = ?,Gender =  ?, Prof = ?, Address = ?, Grade = ? where Id = ?";
        int row = 0;
        try {
            // 获取预处理对象
            statement = connection.prepareStatement(sql);
            statement.setString(1, staff.getSname());
            statement.setString(2, staff.getSno());
            statement.setString(3, staff.getGender());
            statement.setString(4, staff.getProf());
            statement.setString(5, staff.getAddress());
            statement.setString(6, staff.getGrade());
            statement.setString(7, staff.getId());


            // 执行SQL语句
            // select ----> statement.executeQuery();

            // insert delete update
            row = statement.executeUpdate();

        } catch (java.sql.SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DButil.close(resultSet, statement, connection);
        }

        return row;
    }

}

